package org.steven.crm.core.utils;

import com.steven.common.utils.StringUtil;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.*;
import java.util.*;


public class ExcelUtils {
	public static String export(String title, List<String> excelHeaders,
								List<Map<String, String>> datas, String excelDir) {
		HSSFWorkbook wb = new HSSFWorkbook();
	    HSSFSheet sheet = wb.createSheet("sheet1");

	    // 设置字体
	    HSSFFont titlefont = wb.createFont();
	    titlefont.setFontName("黑体");
	    titlefont.setFontHeightInPoints((short) 15);	// 字体大小
	    titlefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);	// 加粗
	    
	    // 设置字体
	    HSSFFont headfont = wb.createFont();
	    headfont.setFontName("黑体");
	    headfont.setFontHeightInPoints((short) 11);	// 字体大小
	    headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);	// 加粗
	    
	    // 另一个样式    
	    HSSFCellStyle titlestyle = wb.createCellStyle();    
	    titlestyle.setFont(titlefont);    
	    titlestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中    
	    titlestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中    
	    titlestyle.setLocked(true);    
	    titlestyle.setWrapText(false);// 自动换行
	    
	    // 另一个样式    
	    HSSFCellStyle headstyle = wb.createCellStyle();
	    headstyle.setFont(headfont);
	    headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中    
	    headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中    
	    
	    // 创建第一行
	    HSSFRow row0 = sheet.createRow(0);
	    row0.setHeight((short) 700);	// 行高
	    HSSFCell cell0 = row0.createCell((excelHeaders.size() / 2) - (1 - excelHeaders.size() % 2));
	    cell0.setCellValue(new HSSFRichTextString(title));    
	    cell0.setCellStyle(titlestyle);
	    
	    HSSFRow row1 = sheet.createRow(1);
	    row1.setHeight((short) 400);
	    for(int i = 0; i < excelHeaders.size(); i++){
	    	sheet.setColumnWidth(i, 4000);
	    	
	    	String[] column_value = StringUtil.split(excelHeaders.get(i), "-");
	    	HSSFCell columns_cell = row1.createCell(i);
	    	columns_cell.setCellStyle(headstyle);
	    	if(column_value.length == 1) {
	    		columns_cell.setCellValue(StringUtil.split(column_value[0], "=")[0]);
	    	} else {
	    		columns_cell.setCellValue(column_value[0]);
	    	}
	    }
	    for (int i = 0; i < datas.size(); i++) {
	    	HSSFRow columnsDe = sheet.createRow(i + 2);
	    	sheet.setColumnWidth(i + 2, 4000);
	    	Map<String, String> data = datas.get(i);
	    	for(int j = 0;j < excelHeaders.size(); j++){
	 	    	String[] column_value = StringUtil.split(excelHeaders.get(j), "-");
	 	    	HSSFCell columns_cell = columnsDe.createCell(j);
	 	    	StringBuffer column_valueAppend = new StringBuffer();
	 	    	if(column_value.length == 1) {
		    		columns_cell.setCellValue(StringUtil.split(column_value[0], "=")[1]);
		    		continue;
		    	} 
	 	    	for(int k =0; k < column_value.length; k++) {
	 	    		if(k > 1) {
	 	    			column_valueAppend.append(data.get(column_value[k]));
	 	    		} else if(k == 1) {
	 	    			column_valueAppend.append(data.get(column_value[1])).append("(");
	 	    		}
	 	    	}
	 	    	columns_cell.setCellValue(column_valueAppend.toString().endsWith("(") 
	 	    			? column_valueAppend.toString().substring(0, column_valueAppend.length() -1)
	 	    					: column_valueAppend.append(")").toString());
	 	    }
	    }

	    if(!new File(excelDir).exists()) {
	    	new File(excelDir).mkdirs();
	    }
		String fileName = new Date().getTime() + ".xls";
	    try {
	    	FileOutputStream os = new FileOutputStream(excelDir + File.separator + fileName);
	    	wb.write(os);
			os.close();
			return fileName;
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public static String export(String title, String[] excelHeaders, List<Map<String, String>> datas, String excelDir){
		HSSFWorkbook wb = new HSSFWorkbook();
	    HSSFSheet sheet = wb.createSheet("sheet1");

	    // 设置字体
	    HSSFFont titlefont = wb.createFont();
	    titlefont.setFontName("黑体");
	    titlefont.setFontHeightInPoints((short) 15);	// 字体大小
	    titlefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);	// 加粗
	    
	    // 设置字体
	    HSSFFont headfont = wb.createFont();
	    headfont.setFontName("黑体");
	    headfont.setFontHeightInPoints((short) 11);	// 字体大小
	    headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);	// 加粗
	    
	    // 另一个样式    
	    HSSFCellStyle titlestyle = wb.createCellStyle();    
	    titlestyle.setFont(titlefont);    
	    titlestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中    
	    titlestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中    
	    titlestyle.setLocked(true);    
	    titlestyle.setWrapText(false);// 自动换行
	    
	    // 另一个样式    
	    HSSFCellStyle headstyle = wb.createCellStyle();
	    headstyle.setFont(headfont);
	    headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中    
	    headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中    
	    
	    // 创建第一行
	    HSSFRow row0 = sheet.createRow(0);
	    row0.setHeight((short) 700);	// 行高
	    HSSFCell cell0 = row0.createCell((excelHeaders.length / 2) - (1 - excelHeaders.length % 2));    
	    cell0.setCellValue(new HSSFRichTextString(title));    
	    cell0.setCellStyle(titlestyle);
	    
	    HSSFRow row1 = sheet.createRow(1);
	    row1.setHeight((short) 400);
	    for(int i = 0; i < excelHeaders.length; i++){
	    	sheet.setColumnWidth(i, 4000);
	    	
	    	String[] column_value = StringUtil.split(excelHeaders[i], "-");
	    	HSSFCell columns_cell = row1.createCell(i);
	    	columns_cell.setCellStyle(headstyle);
	    	if(column_value.length == 1) {
	    		columns_cell.setCellValue(StringUtil.split(column_value[0], "=")[0]);
	    	} else {
	    		columns_cell.setCellValue(column_value[0]);
	    	}
	    }
	    for (int i = 0; i < datas.size(); i++) {
	    	HSSFRow columnsDe = sheet.createRow(i + 2);
	    	sheet.setColumnWidth(i + 2, 4000);
	    	Map<String, String> data = datas.get(i);
	    	for(int j = 0;j < excelHeaders.length; j++){
	 	    	String[] column_value = StringUtil.split(excelHeaders[j], "-");
	 	    	HSSFCell columns_cell = columnsDe.createCell(j);
	 	    	StringBuffer column_valueAppend = new StringBuffer();
	 	    	if(column_value.length == 1) {
		    		columns_cell.setCellValue(StringUtil.split(column_value[0], "=")[1]);
		    		continue;
		    	} 
	 	    	for(int k =0; k < column_value.length; k++) {
	 	    		if(k > 1) {
	 	    			column_valueAppend.append(data.get(column_value[k]));
	 	    		} else if(k == 1) {
	 	    			column_valueAppend.append(data.get(column_value[1])).append("(");
	 	    		}
	 	    	}
	 	    	columns_cell.setCellValue(column_valueAppend.toString().endsWith("(") 
	 	    			? column_valueAppend.toString().substring(0, column_valueAppend.length() -1)
	 	    					: column_valueAppend.append(")").toString());
	 	    }
	    }

	    if(!new File(excelDir).exists()) {
	    	new File(excelDir).mkdirs();
	    }
		String fileName = new Date().getTime()+".xls";
	    try {
	    	FileOutputStream os = new FileOutputStream(excelDir + File.separator + fileName);
	    	wb.write(os);
			os.close();
			return fileName;
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static String export(String[] excelHeaders, List<Map<String, String>> datas, String excelDir) {
		HSSFWorkbook wb = new HSSFWorkbook();
	    HSSFSheet sheet = wb.createSheet("sheet1");

	    HSSFRow columns = sheet.createRow(0);
	    
	    for(int i = 0;i < excelHeaders.length; i++){
	    	sheet.setColumnWidth(i, 4000);
	    	
	    	String[] column_value = StringUtil.split(excelHeaders[i], "-");
	    	HSSFCell columns_cell = columns.createCell(i);
	    	if(column_value.length == 1) {
	    		columns_cell.setCellValue(StringUtil.split(column_value[0], "=")[0]);
	    	} else {
	    		columns_cell.setCellValue(column_value[0]);
	    	}
	    }
	    for (int i = 0; i < datas.size(); i++) {
	    	HSSFRow columnsDe = sheet.createRow(i + 1);
	    	sheet.setColumnWidth(i + 1, 4000);
	    	Map<String, String> data = datas.get(i);
	    	for(int j = 0;j < excelHeaders.length; j++){
	 	    	String[] column_value = StringUtil.split(excelHeaders[j], "-");
	 	    	HSSFCell columns_cell = columnsDe.createCell(j);
	 	    	StringBuffer column_valueAppend = new StringBuffer();
	 	    	if(column_value.length == 1) {
		    		columns_cell.setCellValue(StringUtil.split(column_value[0], "=")[1]);
		    		continue;
		    	} 
	 	    	for(int k =0; k < column_value.length; k++) {
	 	    		if(k > 1) {
	 	    			column_valueAppend.append(data.get(column_value[k]));
	 	    		} else if(k == 1) {
	 	    			column_valueAppend.append(data.get(column_value[1])).append("(");
	 	    		}
	 	    	}
	 	    	columns_cell.setCellValue(column_valueAppend.toString().endsWith("(") 
	 	    			? column_valueAppend.toString().substring(0, column_valueAppend.length() -1)
	 	    					: column_valueAppend.append(")").toString());
	 	    }
	    }

	    if(!new File(excelDir).exists()) {
	    	new File(excelDir).mkdirs();
	    }
		String fileName = new Date().getTime()+".xls";
	    try {
	    	FileOutputStream os = new FileOutputStream(excelDir + File.separator + fileName);
	    	wb.write(os);
			os.close();
			return fileName;
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public static Map<String, List<List<String>>> readExcel(
			String filePath) throws Exception {
		Map<String, List<List<String>>> datas = new HashMap<String, List<List<String>>>();
		InputStream is = null;

		try {
			is = new FileInputStream(filePath);
			WorkbookSettings wkbkSet = new WorkbookSettings();
			wkbkSet.setSuppressWarnings(true);
			Workbook rwb = Workbook.getWorkbook(is, wkbkSet);
			Sheet st[] = rwb.getSheets();

			for (int a = 0; a < st.length; a++) {
				String sheetName = st[a].getName().trim();
				List<List<String>> sheetDatas = new ArrayList<List<String>>();

				for (int i = 0; i < st[a].getRows(); i++) {
					Vector<String> rowDatas = new Vector<String>();
					for (int j = 0; j < st[a].getColumns(); j++) {
						Cell c = st[a].getCell(j, i);
						String content = c.getContents().trim();
						rowDatas.add(content);
					}
					sheetDatas.add(rowDatas);
				}

				datas.put(sheetName, sheetDatas);
			}
			rwb.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				if (is != null) {
					is.close();
				}
			} catch (Exception e) {
			}
		}
		return datas;
	}
	
	/**
	 * 导出订单数据到Excel，并合并单元格
	 * @param excelHeaders	Excel标题
	 * @param datas	需要导出到Excel中的数据
	 * @param mergeLines	需要合并的列，从0计数
	 * @return 
	 * @author caihaifeng
	 */
	public static String exportTableDataWithMergeCells(String title, List<String> excelHeaders,
													   List<Map<String, String>> datas, List<Integer> mergeLines,
													   String excelDir){
		HSSFWorkbook wb = new HSSFWorkbook();
	    HSSFSheet sheet = wb.createSheet("sheet1");
	    
	    // 设置字体
	    HSSFFont titlefont = wb.createFont();
	    titlefont.setFontName("黑体");
	    titlefont.setFontHeightInPoints((short) 15);	// 字体大小
	    titlefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);	// 加粗
	    
	    // 设置字体
	    HSSFFont headfont = wb.createFont();
	    headfont.setFontName("黑体");
	    headfont.setFontHeightInPoints((short) 11);	// 字体大小
	    headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);	// 加粗
	    
	    // 标题样式
	    HSSFCellStyle titlestyle = wb.createCellStyle();    
	    titlestyle.setFont(titlefont);    
	    titlestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中    
	    titlestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中    
	    titlestyle.setLocked(true);    
	    titlestyle.setWrapText(false);// 自动换行
	    
	    // 表头样式
	    HSSFCellStyle headstyle = wb.createCellStyle();
	    headstyle.setFont(headfont);
	    headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
	    headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中

		// 单元格样式
		HSSFCellStyle cellstyle = wb.createCellStyle();
		// 自动换行
		cellstyle.setWrapText(true);
		cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
		cellstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
	    
	    // 创建第一行
	    HSSFRow row0 = sheet.createRow(0);
	    row0.setHeight((short) 700);	// 行高
	    HSSFCell cell0 = row0.createCell(0);
	    cell0.setCellValue(new HSSFRichTextString(title));
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, excelHeaders.size() - 1));
	    cell0.setCellStyle(titlestyle);
	    
	    HSSFRow row1 = sheet.createRow(1);
	    row1.setHeight((short) 400);
	    for(int i = 0; i < excelHeaders.size(); i++){
	    	sheet.setColumnWidth(i, 4000);
	    	
	    	String[] column_value = StringUtil.split(excelHeaders.get(i), "-");
	    	HSSFCell columns_cell = row1.createCell(i);
	    	columns_cell.setCellStyle(headstyle);
	    	if(column_value.length == 1) {
	    		columns_cell.setCellValue(StringUtil.split(column_value[0], "=")[0]);
	    	} else {
	    		columns_cell.setCellValue(column_value[0]);
	    	}
	    }
	    for (int i = 0; i < datas.size(); i++) {
	    	HSSFRow columnsDe = sheet.createRow(i + 2);
	    	sheet.setColumnWidth(i + 2, 4000);
	    	Map<String, String> data = datas.get(i);
	    	for(int j = 0;j < excelHeaders.size(); j++){
	 	    	String[] column_value = StringUtil.split(excelHeaders.get(j), "-");
	 	    	HSSFCell columns_cell = columnsDe.createCell(j);
	 	    	columns_cell.setCellStyle(cellstyle);
	 	    	StringBuffer column_valueAppend = new StringBuffer();
	 	    	if(column_value.length == 1) {
		    		columns_cell.setCellValue(StringUtil.split(column_value[0], "=")[1]);
		    		continue;
		    	} 
	 	    	for(int k =0; k < column_value.length; k++) {
	 	    		if(k > 1) {
	 	    			column_valueAppend.append(data.get(column_value[k]));
	 	    		} else if(k == 1) {
	 	    			column_valueAppend.append(data.get(column_value[1])).append("(");
	 	    		}
	 	    	}
	 	    	columns_cell.setCellValue(column_valueAppend.toString().endsWith("(") 
	 	    			? column_valueAppend.toString().substring(0, column_valueAppend.length() -1)
	 	    					: column_valueAppend.append(")").toString());
	 	    }
	    }
	    // 合并单元格
	    for (int i = 0; i < mergeLines.size(); i++) {
	    	addMergedRegion(sheet, datas, mergeLines.get(i), wb);
	    }
	    if(!new File(excelDir).exists()) {
	    	new File(excelDir).mkdirs();
	    }
		String fileName = title + "_" + new Date().getTime() + ".xls";
	    try {
	    	FileOutputStream os = new FileOutputStream(excelDir + File.separator + fileName);
	    	wb.write(os);
			os.close();
			return fileName;
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**  
     * 合并单元格  
     * @param sheet 要合并单元格的excel 的sheet
     * @param resourceDatas 填充表格的数据
     * @param cellLine  要合并的列 
     */  
	private static void addMergedRegion(HSSFSheet sheet, List<Map<String, String>> resourceDatas, int cellLine, HSSFWorkbook workBook) {
		int firstRow = 2; // 起始行号
		int lastRow = 0; // 终止行号
		int firstCol = cellLine; // 起始列号
		int lastCol = cellLine; // 终止列号
		
		for (int i = 0; i < resourceDatas.size(); i++) {
			Map<String, String> data = resourceDatas.get(i);
			String mergeNo = data.get("mergeNo");
			if (i > 0) {
				if (mergeNo.equals(resourceDatas.get(i - 1).get("mergeNo"))) {
					continue;
				}
			}
			int mergeRowNum = Integer.parseInt(data.get("mergeCellsLineNumber")); // 要合并的行数
			lastRow = firstRow + mergeRowNum - 1;
			sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
			firstRow = lastRow + 1;
		}
	}
}